package com.Util;

import com.Bean.Server;
import net.sf.json.JSONObject;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.HashSet;
import java.util.Set;

public class ServerDao extends DB {
    private static Set<Server> rs2set(ResultSet rs) {
        Set<Server> serverSet = new HashSet<Server>();
        try {
            int i = 0;
            while (rs.next()) {
                Server server = new Server();


                //13
                server.setId(rs.getString("id"));
                server.setUsername(rs.getString("username"));
                server.setName(rs.getString("name"));
                server.setStruct(rs.getString("struct"));
                server.setIpv4(rs.getString("ipv4"));
                server.setCpucore(rs.getString("cpucore"));
                server.setStatus(rs.getString("status"));
                server.setOsname(rs.getString("osname"));
                server.setPrice(rs.getString("price"));
                server.setLaststart(rs.getString("laststart"));


                serverSet.add(server);
                if (DB.isDebug()) {
                    JSONObject jb = JSONObject.fromObject(server);
                    String tojson = jb.toString();
                    System.out.println("server[" + i + "] for select");
                    System.out.println(tojson);
                }

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return serverSet;
    }

    public static Set<Server> selectServerByUser(String username) {
        Set<Server> serverSet = new HashSet<Server>();
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            if (DB.isDebug()) {
                System.out.println("Server.selectServerByUser() processing");
            }
            Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);


            String laststr=new String("");
            if(!UserDao.getUser(username).getStaff().equals("staff"))
            {
                laststr=" where username='" + username + "'";
            }
            ResultSet rs = stmt.executeQuery("select * from " + DBSERVER + laststr);

            int i = 0;
            serverSet = rs2set(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return serverSet;
    }

    /**
     * 传入Server对象，向数据库中增加一条记录，表示用于购买了一种服务器
     *
     * @param server
     * @return 运行状态值, 1表示成功, 0表示失败
     */
    public static int updateServer(Server server) {
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            String sql = "insert into `" + DBSERVER + "` (`username`,`name`,`struct`,`ipv4`,`laststart`,"+
                    "`cpucore`,`cpuhz`,`memcap`,`status`,`osname`,`price`) " +
                    "values (?,?, ?,?, now(),?,?,?,?,?,?);";
            PreparedStatement stat = conn.prepareStatement(sql);
            if (DB.isDebug()) {
                System.out.println("ServerDao.updateUser() processing");
                JSONObject jb = JSONObject.fromObject(server);
                String tojson = jb.toString();
                System.out.println("server for update");
                System.out.println(tojson);
            }

            stat.setString(1, server.getUsername());
            stat.setString(2, server.getName());
            stat.setString(3, server.getStruct());
            stat.setString(4, server.getIpv4());
            stat.setString(5, server.getCpucore());
            stat.setString(6, server.getCpuhz());
            stat.setString(7, server.getMemcap());
            stat.setString(8, server.getStatus());
            stat.setString(9, server.getOsname());
            stat.setString(10, server.getPrice());


            if (DB.isDebug()) {
                System.out.println("Complete stat.setString()");
                System.out.println(stat);
            }
            int result = stat.executeUpdate();
            return result;

        } catch (SQLException e) {
            e.printStackTrace();

            return 0;
        }
    }

    public  static void closeServer(String id,String status){
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            String sql = "UPDATE `server` set `status` = '"+status+"' where id ="+id;

            Statement stat = conn.createStatement();
            if (DB.isDebug()) {
                System.out.println("ServerDao.closeserver() processing");

            }
            stat.execute(sql);

            if (DB.isDebug()) {
                System.out.println("Complete ServerDao.closeserver()");
                System.out.println(stat);
            }
        } catch (SQLException e) {
            e.printStackTrace();

        }
    }
    /**
     * 传入一个User类对象和要删除的服务器的名称
     * 删除数据库中相应的记录，表示用户退订或者服务器到期
     *

     */
    public static void deleteServer(String serverid) {
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            System.out.println("processing deleteserver");
            String sql = "delete from " + DBSERVER + " where id =?";
            PreparedStatement stat = conn.prepareStatement(sql);

            stat.setString(1,  serverid);
            stat.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 传入需牙修改服务信息的用户名，和一个Server对象
     * 将后者的信息写入用户名为username的记录中，更新信息
     *
     * @param username
     * @param server
     * @return
     */
    public static int updateprofile(String username, Server server) {
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            String sql = "update `" + DBSERVER + "` set `ipv4`=?, `name`=? " +
                    "`struct`=?, `laststart`=?, `cpucore`=?, `memcap`=?, `cpuhz`, `price`, `id`, `osname`,"+
                    "`status`=? where `username`=?";
            PreparedStatement stat = conn.prepareStatement(sql);
            stat.setString(1, server.getIpv4());
            stat.setString(2, server.getName());
            stat.setString(3, server.getStruct());
            // String to Date
            java.text.SimpleDateFormat formatter =
                    new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            stat.setDate(4, (Date) formatter.parse(server.getLaststart()));
            stat.setInt(5, Integer.parseInt(server.getCpucore()));
            stat.setString(6, server.getMemcap());
            stat.setString(7, server.getCpuhz());
            stat.setString(8, server.getPrice());
            stat.setString(9, server.getId());
            stat.setString(10, server.getOsname());
            stat.setString(11,server.getStatus());
            stat.setString(12, "'" + username + "'");
            int result = stat.executeUpdate();
            return result;

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 传入int类型的server id号, 根据id来返回该服务器已上机的时长
     * @param id
     * @return 返回 long类型的时间差
     */
    public static long getUptime(String id){
        Set<Server> serverSet;
        connector();
        long uptime = 0L;
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            if (DB.isDebug()) {
                System.out.println("Server.getUptime() processing");
            }
            Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);

            ResultSet rs = stmt.executeQuery("select * from " + DBSERVER + " where `id`=" + id);
            int i = 0;
            serverSet = rs2set(rs);
            for (Server server:serverSet){
                Timestamp last_time = Timestamp.valueOf(server.getLaststart());
                Timestamp current_time = new Timestamp(System.currentTimeMillis());
                uptime = current_time.getTime() - last_time.getTime();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return uptime;
    }

    public static void updateStatus(String username, String id, String status) {
        connector();
        try (Connection conn = DriverManager.getConnection(URL, USER, PWD)) {
            String sql = "update `" + DBSERVER + "` set `status`=? where `username`=? and `id`=?";
            PreparedStatement stat = conn.prepareStatement(sql);
            stat.setString(1, status);
            stat.setString(2, "'" + username + "'");
            stat.setString(3, "'" + status + "'");
            stat.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
